﻿using IBM.Data.DB2;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace TestDB2
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        #region IBM 官方参考

        public void GetConnectionByGeneric()
        {
            DbProviderFactory factory = DbProviderFactories.GetFactory("IBM.Data.DB2");
            DbConnection conn = factory.CreateConnection();
            DbConnectionStringBuilder sb = factory.CreateConnectionStringBuilder();

            if (sb.ContainsKey("Database"))
            {
                sb.Remove("database");
                sb.Add("database", "SAMPLE");
            }

            conn.ConnectionString = sb.ConnectionString;

            conn.Open();
        }

        public void DojobByIBM()
        {


            DB2Connection conn = new DB2Connection(@"Server=172.18.248.22:50001;Database =EDWDB;UID = edw;PWD = edw; Connect Timeout = 30");

            //MinPoolSize   MaxPoolSize ConnectionLifetimeInPool
            conn.Open();

            //update 
            DB2Command cmd = conn.CreateCommand();
            DB2Transaction trans = conn.BeginTransaction();
            cmd.Transaction = trans;
            cmd.CommandText = @"Insert into RM.HZH_GET_DATA_TEMP (CLASS_ID, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10) VALUES('20160101', '', '', '', '', '', '', '', '', '', '')";
            for (int i = 0; i < 14; i++)
            {
                cmd.CommandText = $@"Insert into RM.HZH_GET_DATA_TEMP (CLASS_ID, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10) VALUES('20160101', '{i}', '', '', '', '', '', '', '', '', '')";
                AppendTextAsync($"insert {cmd.ExecuteNonQuery()} records ");
            }

            cmd.CommandText = @"update RM.HZH_GET_DATA_TEMP set class_id='20170101' ";

            cmd.ExecuteNonQuery();

            trans.Commit();
            AppendTextAsync("commit");

            cmd.CommandText = "select * from RM.HZH_GET_DATA_TEMP fetch first 10 rows only ";

            DB2DataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string _s = reader.GetString(1);
               

                AppendTextAsync("output:" + _s);

            }

            reader.Close();
            AppendTextAsync(" single ,ExecuteResultSet");

            DB2ResultSet ds = cmd.ExecuteResultSet(DB2CursorType.ForwardOnly);
            while (ds.Read())
            {
                string _s = ds.GetString(1);
               ;
                AppendTextAsync("output:" + _s);
            }
            ds.Close();



            cmd.CommandText = "begin " +
                  "declare cursor1 cursor with return to client with hold for select * from RM.HZH_GET_DATA_TEMP fetch first 2 rows only; " +
                  "declare cursor2 cursor with return to client with hold for select * from RM.HZH_GET_DATA_TEMP  fetch first 2 rows only; " +
                  "open cursor1; " +
                  "open cursor2; " +
                  "end;";
            //Returns a result set by opened cursor cursor1
            DB2DataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                string _s = dr.GetString(1);
                AppendTextAsync($"output1:{_s}");

            }
            if (dr.NextResult())
            { //Get next result set
                while (dr.Read())
                {
                    string _s = dr.GetString(1);
                    AppendTextAsync($"output2:{_s}");
                }
            }

            dr.Close();

            



        }


        #endregion




        public async void AppendTextAsync(string str)
        {

            await Task.Run(() =>
            {

                Dispatcher.Invoke(delegate
                {
                    rtbLogger.AppendText("[" + DateTime.Now.Ticks + "]  " + str + "\r\n");
                });

            });

        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            DojobByIBM();
        }

    }
}
